using System;
using System.Data;
using System.Data.SqlClient;
using Framework.Library;

namespace Framework.Module.Logging.Statistic
{
    public class StatisticZoneDB
    {
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZones_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch
            {
                return retVal;
            }
            {
                dbConn.Close();
            }
            
            return retVal;
        }
        public static void Delete(int _staticZoneID)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZones_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@StaticZoneID", _staticZoneID);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int Insert(StatisticZoneInfo _statisticZoneInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZones_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@ZoneID", _statisticZoneInfo.ZoneID);
            dbCmd.Parameters.AddWithValue("@Counter", _statisticZoneInfo.Counter);
            dbCmd.Parameters.AddWithValue("@DateAccess", _statisticZoneInfo.Datetime);
            dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int GetIDbyDate(int zoneid, int day, int month, int year)
        {
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand com = new SqlCommand("StatisticZone_GetIDbyDate", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@ZoneID", zoneid);
            com.Parameters.AddWithValue("@day", day);
            com.Parameters.AddWithValue("@month", month);
            com.Parameters.AddWithValue("@year", year);
            try
            {
                con.Open();
                SqlDataReader da = com.ExecuteReader();
                if (da.Read())
                    return da.GetInt32(0);
                else
                    return 0;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(StatisticZoneInfo _statisticZoneInfo)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZones_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@StaticZoneID", _statisticZoneInfo.StaticZoneID);
            dbCmd.Parameters.AddWithValue("@Counter", _statisticZoneInfo.Counter);
            dbCmd.Parameters.AddWithValue("@DateAccess", _statisticZoneInfo.Datetime);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static StatisticZoneInfo GetInfo(int _staticZoneID)
        {
            StatisticZoneInfo retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZones_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.AddWithValue("@StaticZoneID", _staticZoneID);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new StatisticZoneInfo();
                    retVal.StaticZoneID = Convert.ToInt32(dr["StaticZoneID"]);
                    retVal.ZoneID = Convert.ToInt32(dr["ZoneID"]);
                    retVal.Counter = Convert.ToInt32(dr["Counter"]);
                    retVal.Datetime = Convert.ToDateTime(dr["DateAccess"]);
                }
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetFirstAccess()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("StatisticZone_GetFirstAccess", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            catch
            {
                return retVal;
            }
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static int GetTotalbyDate(string zoneids, string dates, string datef)
        {
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand com = new SqlCommand("StatisticZone_GetToTalByDate", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@zoneids", zoneids);
            com.Parameters.AddWithValue("@dates", "'" + dates + "'");
            com.Parameters.AddWithValue("@datef", "'" + datef + "'");
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                    return dr.GetInt32(0);
                else
                    return 0;
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
        }

        public static int GetSumViewContent(string zoneids, int days, int months, int years, int dayf, int monthf, int yearf)
        {
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand com = new SqlCommand("StatisticZone_GetSumViewContent", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@zoneids", zoneids);
            com.Parameters.AddWithValue("@days", days);
            com.Parameters.AddWithValue("@months", months);
            com.Parameters.AddWithValue("@years", years);
            com.Parameters.AddWithValue("@dayf", dayf);
            com.Parameters.AddWithValue("@monthf", monthf);
            com.Parameters.AddWithValue("@yearf", yearf);
            try
            {
                con.Open();
                SqlDataReader dr = com.ExecuteReader();
                if (dr.Read())
                    return dr.GetInt32(0);
                else
                    return 0;
            }
            catch
            {
                return 0;
            }
            finally
            {
                con.Close();
            }
        }

        public static DataTable GetSumbyDate(string zoneids, string dates, string datef, string orderby)
        {
            DataTable tab = null;
            SqlConnection con = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand com = new SqlCommand("StatisticZone_GetSumStatisticByDate", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@zoneids", zoneids);
            com.Parameters.AddWithValue("@dates", "'" + dates + "'");
            com.Parameters.AddWithValue("@datef", "'" + datef + "'");
            com.Parameters.AddWithValue("@orderby", orderby);
            try
            {
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(com);
                tab = new DataTable();
                da.Fill(tab);
                return tab;
            }
            catch
            {
                return tab ;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
